package com.scuec.groupfive.mapper;

import com.scuec.groupfive.dto.UserGrowthDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserGrowthMapper {

    // 按日统计
    @Select("WITH daily_stats AS (" +
            "    SELECT DATE(created_at) AS date, COUNT(*) AS new_users, " +
            "           SUM(COUNT(*)) OVER (ORDER BY DATE(created_at)) AS total_users " +
            "    FROM users " +
            "    GROUP BY DATE(created_at)" +
            ") " +
            "SELECT date, new_users AS count, total_users, " +
            "       ROUND((new_users * 100.0 / NULLIF(LAG(total_users, 1, 0) OVER (ORDER BY date), 0)), 2) AS growth_rate " +
            "FROM daily_stats " +
            "ORDER BY date")
    List<UserGrowthDTO> selectDailyUserGrowth();

    // 按周统计（ISO周，周一为一周开始）
    @Select("WITH weekly_stats AS (" +
            "    SELECT DATE_FORMAT(created_at, '%x-%v') AS year_week, " +
            "           MIN(DATE(created_at)) AS start_date, " +
            "           COUNT(*) AS new_users, " +
            "           SUM(COUNT(*)) OVER (ORDER BY DATE_FORMAT(created_at, '%x-%v')) AS total_users " +
            "    FROM users " +
            "    GROUP BY DATE_FORMAT(created_at, '%x-%v')" +
            ") " +
            "SELECT year_week AS date, start_date, new_users AS count, total_users, " +
            "       ROUND((new_users * 100.0 / NULLIF(LAG(total_users, 1, 0) OVER (ORDER BY year_week), 0)), 2) AS growth_rate " +
            "FROM weekly_stats " +
            "ORDER BY year_week")
    List<UserGrowthDTO> selectWeeklyUserGrowth();

    @Select("WITH monthly_stats AS (" +
            "    SELECT " +
            "      DATE_FORMAT(created_at, '%Y-%m') AS date, " +
            "      COUNT(*) AS count, " +
            "      SUM(COUNT(*)) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')) AS total_users " +
            "    FROM users " +
            "    GROUP BY DATE_FORMAT(created_at, '%Y-%m')" +
            ") " +
            "SELECT " +
            "  date, " +
            "  count, " +
            "  total_users, " +
            "  CASE " +
            "    WHEN LAG(total_users, 1, 0) OVER (ORDER BY date) = 0 THEN 0 " +
            "    ELSE ROUND((count * 100.0 / LAG(total_users, 1) OVER (ORDER BY date)), 2) " +
            "  END AS growth_rate " +  // 确保这里使用growth_rate
            "FROM monthly_stats " +
            "ORDER BY date")
    List<UserGrowthDTO> selectMonthlyUserGrowth();

    // 按年统计
    @Select("WITH yearly_stats AS (" +
            "    SELECT YEAR(created_at) AS year, " +
            "           COUNT(*) AS new_users, " +
            "           SUM(COUNT(*)) OVER (ORDER BY YEAR(created_at)) AS total_users " +
            "    FROM users " +
            "    GROUP BY YEAR(created_at)" +
            ") " +
            "SELECT year AS date, new_users AS count, total_users, " +
            "       ROUND((new_users * 100.0 / NULLIF(LAG(total_users, 1, 0) OVER (ORDER BY year), 0)), 2) AS growth_rate " +
            "FROM yearly_stats " +
            "ORDER BY year")
    List<UserGrowthDTO> selectYearlyUserGrowth();
}